package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.Properties;

import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;


public class SQLModel {
	public Connection conn;
	public SQLModel(Connection conn){
		this.conn = conn;
	}
	
	public static final String DEF_PROFILE = "SALER_PROFILE";
	public static final String DEF_ROLE = "SALER_ROLE";	
	public static final String DEF_STAT_TYPE = "SELECT,UPDATE,INSERT,DELETE";	

	public void connect(String username, String password)throws Exception{
		conn.close();
		conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.133.130:1521:orcl", username,password);	
		
	}
	public void alterPassword(String username,String newPass)throws Exception{
		String alterPassStr= " alter user " +username  +
				  			" identified by "+newPass;

		MyCrypto crypto = new MyCrypto();
		String encryptPass=crypto.encryptString(newPass, "DES","DES/ECB/PKCS5Padding");
		
		String updateAccStr = " update sys.account "+
				  			  " set password = ?"+
							  " where username = ?";
		PreparedStatement alterPassStat = conn.prepareStatement(alterPassStr);
		PreparedStatement updateAccStat  = conn.prepareStatement(updateAccStr);
		updateAccStat.setString(1, encryptPass)	;
		updateAccStat.setString(2, username.toUpperCase());
		alterPassStat.execute();
		updateAccStat.execute();
		alterPassStat.close();
		updateAccStat.close();
	}
	public void alterOtherInfo(String username, String status, String quota, String role, String profile)throws Exception{
		String alterUserStr = " alter user " +username  +
							  " account " + status +
							  " quota "+ quota + " on users "+
							  " profile "+ profile;
		String updateAccStr = " update sys.account "+
							  " set status = ?,quota = ?,profile = ? "+
							  " where username = ?";	  	
							
		PreparedStatement alterUserStat = conn.prepareStatement(alterUserStr);
		PreparedStatement updateAccStat = conn.prepareStatement(updateAccStr);
		
		
		updateAccStat.setString(1, status);
		updateAccStat.setString(2, quota);
		updateAccStat.setString(3, profile);
		updateAccStat.setString(4,username.toUpperCase());
		alterUserStat.execute();
		updateAccStat.execute();	
		alterUserStat.close();
		updateAccStat.close();
		
		//String revokeStr 
	}
	public void grantExtend(String grantee, String owner, String obj )throws Exception{
		String sqlStr = " grant all on "+owner+"."+obj +" to "+grantee;
		String insStr = " insert into "+grantee+".storetable values(?,?) ";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		PreparedStatement insStat = conn.prepareStatement(insStr);
		insStat.setString(1,owner);
		insStat.setString(2,obj);	
		sqlStat.execute();
		insStat.execute();
		sqlStat.close();
		insStat.close();
	}
	
	
	public void initUser(String username) throws Exception{
		String createStr1 = " create table "+username+"."+"storeTable(" +
							" owner varchar2(20)," +
							" obj varchar2(20) )";
		String createStr2 = " create table "+username+"."+"saleReport(" +
				 " report_id         NUMBER, " +
				 " item_id         VARCHAR2(5), " +
				 "  saleDate        DATE, " +
				 " num_items         NUMBER, " +
				 "  PRIMARY KEY (report_id), " +
				 " CONSTRAINT fk_"+username+
				 " FOREIGN KEY (item_id) "+
				 " REFERENCES admin.item(item_id) "+
				 
				" ) ";
		
		String grantStr1 = " grant all on admin.item to "+ username ;
		String grantStr2 = " grant all on  admin.fgatable to "+ username ;
		String grantStr3 = " grant select,update on sys.account to "+ username ;
		String grantStr4 = " grant all on admin.id_seq to "+ username ;
		String insStr1 = " insert into "+username+"."+"storeTable values ("+
						 " 'ADMIN', 'ITEM' )";	
		String insStr2 = "insert into "+username+"."+"storeTable values (?"+",'SALEREPORT')";
		String insStr3 = "insert into admin.storeTable values (?"+",'SALEREPORT')";
		
		PreparedStatement createStat1 = conn.prepareStatement(createStr1);
		PreparedStatement createStat2 = conn.prepareStatement(createStr2);
		PreparedStatement grantStat1 = conn.prepareStatement(grantStr1);
		PreparedStatement grantStat2 = conn.prepareStatement(grantStr2);
		PreparedStatement grantStat3 = conn.prepareStatement(grantStr3);
		PreparedStatement grantStat4 = conn.prepareStatement(grantStr4);
		PreparedStatement insStat1 = conn.prepareStatement(insStr1);
		PreparedStatement insStat2 = conn.prepareStatement(insStr2);
		PreparedStatement insStat3 = conn.prepareStatement(insStr3);
		insStat2.setString(1, username.toUpperCase());
		insStat3.setString(1,username.toUpperCase());
		grantStat1.execute();
		grantStat2.execute();
		grantStat3.execute();
		grantStat4.execute();
		createStat1.execute();
		createStat2.execute();
		insStat3.execute();
		insStat1.execute();
		insStat2.execute();
		grantStat1.close();
		grantStat2.close();
		grantStat3.close();
		grantStat4.close();
		createStat1.close();
		createStat2.close();
		insStat1.close();
		insStat2.close();
		insStat3.close();
	
	}
	public boolean isAuditStandard(String username)throws Exception{
		boolean isAudit = false;
		String sqlStr = " select count(*) from admin.standardtable" +
						" where username = ? ";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,username);
		ResultSet res = sqlStat.executeQuery();
		
		while(res.next()){
			if(!res.getString(1).equals("0")) isAudit = true;
		
		}
		System.out.println(username+"Is audited standard " +isAudit);
		sqlStat.close();
		return isAudit;
	}
	public boolean isAuditWhole()throws Exception{
		boolean isAuditWhole = true;
		String sqlStr = "select * from admin.auditwhole";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			if(res.getString(1).equalsIgnoreCase("N")) isAuditWhole = false;
		}
		System.out.println("Is Audit Whole: "+isAuditWhole);
		sqlStat.close();
		return isAuditWhole;
	}
	public void updateAuditWholeTable(String u)throws Exception{
	
		String sqlStr = "update admin.auditwhole set isAuditedWhole = ?";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,u);
		sqlStat.execute();
		sqlStat.close();
		
	}

	public void createUser(String username, String password, String quota, String profile, String role)throws Exception{
		String createStr = "create user " +username +" identified by "+ password+
				              " quota " + quota + " on users"+
				              " profile " + profile;
		String grantStr = "grant "+ role + " to "+ username;
		String insertToAccTabStr = "insert into sys.account values (?,?,'UNLOCK',?,?,?)";
		
		MyCrypto crypto = new MyCrypto();
		String encryptPass=crypto.encryptString(password, "DES","DES/ECB/PKCS5Padding");
		
		
		PreparedStatement insertToAccTabStat = conn.prepareStatement(insertToAccTabStr);
		insertToAccTabStat.setString(1, username.toUpperCase());
		insertToAccTabStat.setString(2, encryptPass);
		insertToAccTabStat.setString(3, quota.toUpperCase());
		insertToAccTabStat.setString(4, profile.toUpperCase());
		insertToAccTabStat.setString(5, role.toUpperCase());
		PreparedStatement createStat= conn.prepareStatement(createStr);
		PreparedStatement grantStat = conn.prepareStatement(grantStr);
		createStat.execute();
		grantStat.execute();
		insertToAccTabStat.execute();
		initUser(username);
		createStat.close();
		grantStat.close();
		insertToAccTabStat.close();
				
	}
	public void initUsers(String username, String password, String quota, String profile, String role)throws Exception{

		String insertToAccTabStr = "insert into sys.account values (?,?,'UNLOCK',?,?,?)";
		
		MyCrypto crypto = new MyCrypto();
		String encryptPass=crypto.encryptString(password, "DES","DES/ECB/PKCS5Padding");
		System.out.println("Init "+username);
		
		PreparedStatement insertToAccTabStat = conn.prepareStatement(insertToAccTabStr);
		insertToAccTabStat.setString(1, username.toUpperCase());
		insertToAccTabStat.setString(2, encryptPass);
		insertToAccTabStat.setString(3, quota.toUpperCase());
		insertToAccTabStat.setString(4, profile.toUpperCase());
		insertToAccTabStat.setString(5, role.toUpperCase());
		
		insertToAccTabStat.execute();

		insertToAccTabStat.close();
				
	}

	public void dropUser(String username)throws Exception {
		String dropUserStr = " drop user "+ username +" cascade ";
		String updateAccStr = " delete from sys.account "+
							  " where username = ? ";
		String updateStoreTableAdmin = " delete from admin.storetable "+
				  " where owner = ? ";
		PreparedStatement dropUserStat= conn.prepareStatement(dropUserStr);
		PreparedStatement updateAccStat = conn.prepareStatement(updateAccStr);
		PreparedStatement updateStoreTableAdminStat = conn.prepareStatement(updateStoreTableAdmin);
		updateAccStat.setString(1, username.toUpperCase());
		updateStoreTableAdminStat.setString(1,username.toUpperCase());
		dropUserStat.execute();
		updateAccStat.execute();
		updateStoreTableAdminStat.execute();
		updateAccStat.close();
		dropUserStat.close();
		updateStoreTableAdminStat.close();
	}
	public boolean isExistingUser(String username) throws Exception{
		boolean isExist = true;
		String getUsernameStr=" select count(*) from sys.account " +
								"where username = ?";
		PreparedStatement getUsernameStat= conn.prepareStatement(getUsernameStr);
		getUsernameStat.setString(1, username.toUpperCase());
		ResultSet rs= getUsernameStat.executeQuery();
		while (rs.next()) {
			System.out.println("------------>"+rs.getString(1));
			if(rs.getString(1).equalsIgnoreCase("0")) isExist = false;
		}
		getUsernameStat.close();
		System.out.println(isExist);
		return isExist;
	}
	public UserInfo getUserInfo(String username) throws Exception{
		UserInfo usrInfo = new UserInfo();
		String getUserInfoStr=" select * from sys.account " +
							  " where username = ?";
		PreparedStatement getUserInfoStat= conn.prepareStatement(getUserInfoStr);
		getUserInfoStat.setString(1, username.toUpperCase());
		ResultSet rs = getUserInfoStat.executeQuery();
		while (rs.next()) {
			System.out.println("------->"+rs.getString(1));
			usrInfo.username = rs.getString(1);
			usrInfo.password = rs.getString(2);
			usrInfo.status = rs.getString(3);
			usrInfo.quota = rs.getString(4);
			usrInfo.profile = rs.getString(5);
			usrInfo.role = rs.getString(6);	
		}
		System.out.println(usrInfo.username+usrInfo.password+
				usrInfo.status+usrInfo.quota+usrInfo.profile);
		getUserInfoStat.close();
		return usrInfo;
	}
	
	public LinkedList<UserInfo> getAllUserInfo() throws Exception{
		LinkedList<UserInfo> resInfoList = new LinkedList<UserInfo>();
		
		String getUserInfoStr=" select * from sys.account ";
		PreparedStatement getUserInfoStat= conn.prepareStatement(getUserInfoStr);
		ResultSet rs = getUserInfoStat.executeQuery();
		while (rs.next()) {
			UserInfo usrInfo = new UserInfo();
			System.out.println("------->"+rs.getString(1));
			usrInfo.username = rs.getString(1);
			usrInfo.password = rs.getString(2);
			usrInfo.status = rs.getString(3);
			usrInfo.quota = rs.getString(4);
			usrInfo.profile = rs.getString(5);
			usrInfo.role = rs.getString(6);	
			resInfoList.add(usrInfo);
		}
		
		getUserInfoStat.close();
		return resInfoList;
	}
	
	
	public LinkedList<String> getAllUsers() throws Exception{
		LinkedList <String> resList= new LinkedList<String>();
		String getAllUsersStr =" select username from sys.account ";
		PreparedStatement getAllUsersStat = conn.prepareStatement(getAllUsersStr);
	
		ResultSet rs= getAllUsersStat.executeQuery();
		while (rs.next()) {
			resList.add(rs.getString(1));
		}
		System.out.println("Users are: "+resList);
		getAllUsersStat.close();
		return resList;
	}
	public void printAllTable( LinkedList<Table>lst){
		System.out.println("ALL TABLES : ");
		for(Table tab:lst){
			System.out.println(tab.ownerName+"."+tab.tableName+"    "+tab.isAudited);
		}
	}
	public LinkedList<Table> getAllTable()throws Exception{
		LinkedList <Table> resList= new LinkedList<Table>();
		String getAllTablesStr = 
				" select a.owner, a.table_name from all_tables a inner join dba_users b"+
				" on b.profile = ?  and a.owner = b.username "+
				" order by a.owner";
		PreparedStatement getAllTablesStat = conn.prepareStatement(getAllTablesStr);
		getAllTablesStat.setString(1, DEF_PROFILE);
		ResultSet rs = getAllTablesStat.executeQuery();
		while(rs.next()){
			if(!rs.getString(2).equals("FGATABLE")&&!rs.getString(2).equals("STORETABLE")){
				Table tab = new Table(rs.getString(1),rs.getString(2));
				resList.add(tab);
			}
		}
		getAllTablesStat.close();
		printAllTable(resList);
		return resList;
	}


	public LinkedList<String> getAllTables()throws Exception{
		LinkedList <String> resList= new LinkedList<String>();
		String getAllTablesStr = 
				" select a.owner,a.table_name from all_tables a inner join dba_users b"+
				" on b.profile = ?  and a.owner = b.username "+
				" order by a.owner";
		PreparedStatement getAllTablesStat = conn.prepareStatement(getAllTablesStr);
		getAllTablesStat.setString(1, DEF_PROFILE);
		ResultSet rs = getAllTablesStat.executeQuery();
		while(rs.next()){
			resList.add(rs.getString(1)+"."+rs.getString(2));
		}
		System.out.println(resList);
		return resList;
	}

	public LinkedList<Table> getAllTablesOfUser(String username)throws Exception{
			LinkedList <Table> resList= new LinkedList<Table>();
			String getTablesStr =
					" select owner, obj from "+ username+".storeTable "+
					" where owner = ?";
								
			PreparedStatement getTablesStat = conn.prepareStatement(getTablesStr);
			getTablesStat.setString(1, username.toUpperCase());
			ResultSet rs= getTablesStat.executeQuery();
			while (rs.next()) {
				Table tab = new Table(username.toUpperCase(),rs.getString(2));
				resList.add(tab);
				
			}
			printAllTable(resList);
			getTablesStat.close();
			return resList;
	}
	public LinkedList<Table> eliminateList1(LinkedList<Table> lst1,LinkedList<Table> lst2 ){
	//	if(lst2.isEmpty())return lst1;
		System.out.println("LstTb1:");
		printAllTable(lst1);
		System.out.println("LstTb2:");
		printAllTable(lst2);
		LinkedList<Table>resList = new LinkedList<Table>();
		for(Table t1:lst1){		
			for(Table t2:lst2){			
				if(t2.tableName.equals(t1.tableName)&&t2.ownerName.equals(t1.ownerName))t1.setAudit(true);
			}
			resList.add(t1);
		}
		return resList;
	}
	public LinkedList<User> eliminateList2(LinkedList<User> lst1,LinkedList<User> lst2 ){
		if(lst2.isEmpty())return lst1;
		LinkedList<User>resList = new LinkedList<User>();
		for(User u1:lst1){		
			for(User u2:lst2){			
				if(u2.username.equals(u1.username)) u1.auditType = u2.auditType;
			
			}
			resList.add(u1);
		}
		return resList;
	}
	public LinkedList<Table> getAccessTablesOfUser(String username)throws Exception{
		LinkedList <Table> resList= new LinkedList<Table>();
		String getTablesStr =
				" select owner, obj from "+ username+".storeTable ";
				
		PreparedStatement getTablesStat = conn.prepareStatement(getTablesStr);
		
		ResultSet rs= getTablesStat.executeQuery();
		while (rs.next()) {
			Table tab = new Table(rs.getString(1),rs.getString(2));	
			resList.add(tab);
		}
		printAllTable(resList);
		getTablesStat.close();
		return resList;
	}
	public void printUserList(LinkedList<User>lst){
		for(User u : lst){
			System.out.println("username: "+u.username);
			System.out.println("statTypes: "+u.auditType);
		}
		
	}
	public LinkedList<User> getListAuditUsers(String granter,String owner, String obj)throws Exception{
		LinkedList <User> resLst = new LinkedList<User>();
		LinkedList <User> subLst= new LinkedList<User>();
		LinkedList <User> superLst = new LinkedList<User>();
		String getAllUsersStr =" select username from sys.account ";
		PreparedStatement getAllUsersStat = conn.prepareStatement(getAllUsersStr);
	
		ResultSet rs= getAllUsersStat.executeQuery();
		while (rs.next()) {
			User u = new User(rs.getString(1),"");
			superLst.add(u);
		}
		
		String getSubListStr =
				" select grantee, statTypes from  admin.fgatable  "+
						" where granter =? and owner = ? and obj = ?";
		PreparedStatement getSubListStat = conn.prepareStatement(getSubListStr);
		getSubListStat.setString(1,granter.toUpperCase());
		getSubListStat.setString(2,owner.toUpperCase());	
		getSubListStat.setString(3,obj.toUpperCase());		
		ResultSet rs1= getSubListStat.executeQuery();
		while (rs1.next()) {
			User user = new User(rs1.getString(1),rs1.getString(2));	
			subLst.add(user);
		}
		resLst = eliminateList2(superLst,subLst);
		printUserList(resLst);
		return resLst;
		
		
		//return eliminateList1(superLst, subLst);
	}

	public LinkedList<Table> getListAuditTables(String granter, String grantee)throws Exception{
		LinkedList <Table> resLst= new LinkedList<Table>();
		LinkedList <Table> subLst= new LinkedList<Table>();
		LinkedList <Table> superLst= new LinkedList<Table>();
		String getSubListStr =
				" select A.owner, A.obj from  "+ grantee+".storeTable A left outer join admin.fgatable B on A.owner= B.owner and A.obj = B.obj " +
						"where B.granter = ? and B.grantee = ?";
		
		PreparedStatement getSubListStat = conn.prepareStatement(getSubListStr);
		getSubListStat.setString(1,granter.toUpperCase());
		getSubListStat.setString(2,grantee.toUpperCase());	
		ResultSet rs= getSubListStat.executeQuery();
		while (rs.next()) {
			Table tab = new Table(rs.getString(1),rs.getString(2));	
			subLst.add(tab);
		}
		String getTablesStr =
				" select owner, obj from "+ grantee+".storeTable ";
							
		PreparedStatement getTablesStat = conn.prepareStatement(getTablesStr);
	
		ResultSet rs1= getTablesStat.executeQuery();
		while (rs1.next()) {
			Table tab = new Table(rs1.getString(1),rs1.getString(2));
			superLst.add(tab);	
		}
		System.out.println("Tables are: "+superLst);
		resLst=eliminateList1(superLst, subLst);
		printAllTable(resLst);
		return resLst;
	}
	/*public LinkedList<Table> getUnAccessTablesOfUser(String username)throws Exception{
		LinkedList<Table>resList = new LinkedList<Table>();
		LinkedList<Table>lst1 = getAllTable();
		LinkedList<Table>lst2 = getAccessTablesOfUser(username);
		resList = eliminateList(lst1,lst2);
		printAllTable(resList);	
		return resList;
	}*/
	//check duplicate user for func create user
	
	
	
	public void selectAccount(String username) throws Exception{
		String sql=" Select * from sys.account where username = ?";
		PreparedStatement st= conn.prepareStatement(sql);
		st.setString(1, username);
		ResultSet rs= st.executeQuery();
		
		while (rs.next()) {
			System.out.println("Hello");
			String result="";
			String t= rs.getString(1);
			
			String t1=rs.getString(2);
			result=result+t+" " +t1;
			System.out.println(result);
		}
	
		st.close();
	}

	public void createUser(String user, String pass) throws Exception{
		String sql=" create user "+user+" identified by "+pass;
		PreparedStatement st= conn.prepareStatement(sql);
		st.execute();
		String sql1=" grant create session to "+user;
		PreparedStatement st1= conn.prepareStatement(sql1);
		st1.execute();
		st.close();
		st1.close();
		
	}
	
	public void enableStandard(String username)throws Exception{
		String enAuditStr = "audit all by " + username +" by access" ;
		PreparedStatement enAuditStat = conn.prepareStatement(enAuditStr);
		enAuditStat.execute();

		enAuditStat.close();
		//delStat.close();
	}
	public void disableStandard(String username)throws Exception{
		String noAuditStr = "noaudit all by " + username ;
		PreparedStatement noAuditStat = conn.prepareStatement(noAuditStr);
		noAuditStat.execute();

		noAuditStat.close();
		//delStat.close();
	}
	public void disableAllStandard() throws Exception{
		String selStr = "select username from admin.standardtable ";
		PreparedStatement selStat =conn.prepareStatement(selStr);
		ResultSet res = selStat.executeQuery();
		while(res.next()){
			System.out.println("----------->"+res.getString(1));
			disableStandard(res.getString(1));
		}
		res.close();
	}
	public void enableAllStandard() throws Exception{
		String selStr = "select username from admin.standardtable ";
		PreparedStatement selStat =conn.prepareStatement(selStr);
		ResultSet res = selStat.executeQuery();
		while(res.next()){
			System.out.println("----------->"+res.getString(1));
			enableStandard(res.getString(1));
		}
		res.close();
	}
	public void disableFGAPolicy(String policyName,String owner,String obj)throws Exception{
		String sqlStr = " Begin " +
						" dbms_fga.disable_policy(" +
						" object_schema => ?, "+
						" object_name => ?, " +
						" policy_name => ?);" +
						"end;";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,owner.toUpperCase());
		sqlStat.setString(2,obj.toUpperCase());
		sqlStat.setString(3,policyName.toUpperCase());
		sqlStat.execute();
		sqlStat.close();
						
						
	}
	public void enableFGAPolicy(String policyName,String owner,String obj)throws Exception{
		String sqlStr = " Begin " +
						" dbms_fga.enable_policy(" +
						" object_schema => ?, "+
						" object_name => ?, " +
						" policy_name => ?," +
						" enable => TRUE);" +
						"end; ";
		
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,owner.toUpperCase());
		sqlStat.setString(2,obj.toUpperCase());
		sqlStat.setString(3,policyName.toUpperCase());
		sqlStat.execute();
		sqlStat.close();
						
						
	}
	public void dropAllFGAPolicy() throws Exception{
		PreparedStatement sqlStat = conn.prepareStatement("select * from admin.fgatable ");
		String policyName = "";
		int id=0;
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			id = Integer.parseInt(res.getString(1));
			policyName = "policy"+id;
			dropFGA(id, res.getString(4), res.getString(5));
		}
	}
	
	public void disableAllFGAPolicy()throws Exception{
		PreparedStatement sqlStat = conn.prepareStatement("select * from admin.fgatable ");
		String policyName = "";
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			policyName = "policy"+res.getString(1);
			disableFGAPolicy(policyName, res.getString(4), res.getString(5));
		}
	}
	public void enableAllFGAPolicy()throws Exception{
		PreparedStatement sqlStat = conn.prepareStatement("select * from admin.fgatable ");
		String policyName = "";
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			policyName = "policy"+res.getString(1);
	    	enableFGAPolicy(policyName, res.getString(4), res.getString(5));
		}
	}
	
	public void noAuditStandard(String username)throws Exception{
		String noAuditStr = "noaudit all by " + username ;
		String delStr = "delete from standardTable where username = ?";
		PreparedStatement delStat = conn.prepareStatement(delStr);
		delStat.setString(1,username.toUpperCase());
		PreparedStatement noAuditStat = conn.prepareStatement(noAuditStr);
		noAuditStat.execute();
		delStat.execute();
		noAuditStat.close();
		delStat.close();
	}
	
	public void auditStandard(String username,String whenOption)throws Exception{
		String auditStr = "audit all by " + username + " by access " + whenOption ;
		String insStr = "insert into standardTable values(?)";
		PreparedStatement insStat = conn.prepareStatement(insStr);
		insStat.setString(1,username.toUpperCase());
		PreparedStatement auditStat = conn.prepareStatement(auditStr);
		auditStat.execute();
		insStat.execute();
		auditStat.close();
		insStat.close();
	}
	public void printLogin(LinkedList<LoginLog>lst){
		for(LoginLog l : lst){
			System.out.println("Field1 "+l.field1 +"...........Field2: "+l.field2);
			
		}
		
	}
	public LinkedList<LoginLog> viewChangePassAction(String username)throws Exception{
		LinkedList<LoginLog> resLst = new LinkedList<LoginLog>();
		
		String selStr = "select distinct to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') TIMECHANGE from dba_audit_trail " +
						" where username = ? and action_name = 'ALTER USER' " +
						" order by TIMECHANGE desc";
		PreparedStatement sqlStat = conn.prepareStatement(selStr);
		sqlStat.setString(1,username.toUpperCase());
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			LoginLog log = new LoginLog(res.getString(1),"ALTER USER");
			resLst.add(log);
		}
		System.out.println("Time change Pass: ");
		printLogin(resLst);
		if(resLst.size() == 0) return null;
		return resLst;
		
		
	}
	public LinkedList<LoginLog> viewLogin(String username)throws Exception{
		LinkedList <LoginLog> resLst = new LinkedList <LoginLog>();
		String selStr = " select distinct to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') LOGONTIME, " +
						" to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS') LOGOFFTIME" +
						" from dba_audit_trail" +
						" where username = ? and (action_name = 'LOGOFF' or action_name = 'LOGON')" +
						" order by LOGONTIME desc ";
		
		PreparedStatement selStat = conn.prepareStatement(selStr);
		selStat.setString(1,username);
		ResultSet res = selStat.executeQuery();
		while(res.next()){
			LoginLog log = new LoginLog(res.getString(1),res.getString(2));
			resLst.add(log);
		}
		printLogin(resLst);
		if(resLst.size()==0) return null;
		return resLst;
		
	}
	/*public void auditAllByUser(String username, String byOption, String whenOption)throws Exception{
	    String audit4StatStr = "audit select table, insert table, update table, delete table by " + username + "  " + byOption + "  "+whenOption;
	    String auditSelectAnyStr = "audit select any table by "+ username+ "  "+ byOption + " " + whenOption;
	    String auditUpdateAnyStr = "audit update any table by "+ username+ "  "+ byOption + " " +  whenOption;
	    String auditInsertAnyStr = "audit insert any table by "+ username+ "  "+ byOption + " " + whenOption;
	    String auditDeleteAnyStr = "audit delete any table by "+ username+ "  "+ byOption + " " +  whenOption;
	    
	    String updateAccStr = " update admin.account " +
	    					   " set isAuditAll = 'Y' " +
	    					   " where username = ?";
	    PreparedStatement updateAccStat = conn.prepareStatement(updateAccStr);						
	    PreparedStatement audit4StatStat = conn.prepareStatement(audit4StatStr);
	    PreparedStatement auditSelectAnyStat = conn.prepareStatement(auditSelectAnyStr);						
	    PreparedStatement auditInsertAnyStat = conn.prepareStatement(auditInsertAnyStr);						
	    PreparedStatement auditUpdateAnyStat = conn.prepareStatement(auditUpdateAnyStr);						
	    PreparedStatement auditDeleteAnyStat = conn.prepareStatement(auditDeleteAnyStr);						
	    updateAccStat.setString(1, username.toUpperCase());
	    audit4StatStat.execute();
	    auditSelectAnyStat.execute();
	    auditInsertAnyStat.execute();
	    auditUpdateAnyStat.execute();
	    auditDeleteAnyStat.execute();
		updateAccStat.execute();
		audit4StatStat.close();
		auditSelectAnyStat.close();
		auditInsertAnyStat.close();
		auditUpdateAnyStat.close();
		auditDeleteAnyStat.close();
		updateAccStat.close();
		
	}*/
	/*public boolean hasAuditAll(String username)throws Exception{
		boolean isExist = false;
		String ckStr = " select isAuditAll from admin.account " +
				" where username = ? ";
		PreparedStatement ckStat = conn.prepareStatement(ckStr);
		ckStat.setString(1, ckStr);
		ResultSet rs= ckStat.executeQuery();
		while (rs.next()) {
			if(rs.getString(1).equalsIgnoreCase("Y")) isExist = true;
		}
		ckStat.close();
		return isExist;
	}*/
	/*public boolean isExistingFGAPolicy(String username,String obj, String owner)throws Exception{
		boolean isExist = false;
		String policyName = username.toLowerCase()+owner.toLowerCase()+obj.toLowerCase();
		String getPolicyStr = " select count(*) from admin.FGATable " +
							" where policyName = ? ";
		PreparedStatement getPolicyStat = conn.prepareStatement(getPolicyStr);
		getPolicyStat.setString(1, policyName);
		ResultSet rs= getPolicyStat.executeQuery();
		while (rs.next()) {
			System.out.println("------------>"+rs.getString(1));
			if(!rs.getString(1).equalsIgnoreCase("0")) isExist = true;
		}
		getPolicyStat.close();
		return isExist;
	}*/
	public void insertFGATable(String granter, String grantee, String owner, String obj,String statTypes)throws Exception{
		String sqlStr = "insert into admin.FGATable values (admin.id_seq.nextval,?,?,?,?,?)";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, granter.toUpperCase());
		sqlStat.setString(2, grantee.toUpperCase());
		sqlStat.setString(3, owner.toUpperCase());
		sqlStat.setString(4, obj.toUpperCase());
		sqlStat.setString(5,statTypes.toUpperCase());
		sqlStat.execute();
		sqlStat.close();
		
	}
	//chua check
	/*public void auditAllByUser(String username)throws Exception{
		LinkedList<Table>lstTables = getAllTable();
		for(Table table:lstTables){
			createFGAPolicy(username,table.tableName,table.ownerName,DEF_STAT_TYPE,"USER_ACT");
		}
	}
	public void dropAuditAllByUser(String username)throws Exception{
		LinkedList<Table>lstTables = getAllTable();
		for(Table table:lstTables){
			dropFGAPolicy(username,table.tableName,table.ownerName,"USER_ACT");
		}
	}*/
	/*public void auditAllByObjAdminSetting(String owner,String obj)throws Exception{
		LinkedList<String>lstUsers = getAllUsers(DEF_PROFILE);
		for(String user:lstUsers){
			createFGAPolicy(user,obj,owner,DEF_STAT_TYPE,"ADMIN_OBJ");
		}
	}
	
	public void auditAllByObjUserSetting(String owner,String obj)throws Exception{
		LinkedList<String>lstUsers = getAllUsers(DEF_PROFILE);
		for(String user:lstUsers){
			createFGAPolicy(user,obj,owner,DEF_STAT_TYPE,"USER_OBJ");
		}
	}*/
	public LinkedList<Table> eliminateList(LinkedList<Table> lst1,LinkedList<Table> lst2 ){
		if(lst2.isEmpty())return lst1;
		LinkedList<Table>resList = new LinkedList<Table>();
		for(Table t1:lst1){
			boolean isExist = false;
			for(Table t2:lst2){
				if(isExist)break;
				if(t2.tableName.equals(t1.tableName)&&t2.ownerName.equals(t1.ownerName))isExist=true;		
			}
			if(!isExist){
				Table t = new Table(t1.ownerName,t1.tableName);
				resList.add(t);
			}
		}
		return resList;
	}
	/*public LinkedList<String> getUnAuditedUserAdminSetting(String owner,String obj)throws Exception{
		LinkedList<String>allUsersLst = getAllUsers(DEF_PROFILE);
		LinkedList<String>auditedUsersLst = getUserByObjAdminSetting(owner,obj);
		if(auditedUsersLst.isEmpty())return allUsersLst;
		LinkedList<String>resLst = new LinkedList<String>();
		for(String s:allUsersLst){
			if(!auditedUsersLst.contains(s))resLst.add(s);
		}
		System.out.println("Unaudited users on : "+owner+"."+obj+" Usermode "+resLst);
		return resLst;
		
	}*/
	/*public LinkedList<String> getUnAuditedUserUserSetting(String owner,String obj)throws Exception{
		LinkedList<String>allUsersLst = getAllUsers(DEF_PROFILE);
		LinkedList<String>auditedUsersLst = getUserByObjUserSetting(owner,obj);
		if(auditedUsersLst.isEmpty())return allUsersLst;
		LinkedList<String>resLst = new LinkedList<String>();
		for(String s:allUsersLst){
			if(!auditedUsersLst.contains(s))resLst.add(s);
		}
		System.out.println("Unaudited users on : "+owner+"."+obj+" AdminMode "+resLst);
		return resLst;
		
	}*/
	/*public LinkedList<Table> getUnAuditedObjAdminSetting(String username)throws Exception{
		LinkedList<Table>resList = new LinkedList<Table>();
		LinkedList<Table>lst1 = getAllTable();
		LinkedList<Table>lst2 = getObjsByUserAdminSetting(username);
		resList = eliminateList(lst1,lst2);
		printAllTable(resList);
		return resList;
		
		
	}*/
	public void createFGAPolicy(String granter, String grantee, String owner,String obj, String statTypes)throws Exception{
		//if(hasAuditAll(username) || isExistingFGAPolicy(username,obj,owner)) return;
		String policyName = "";
		insertFGATable(granter,grantee,owner,obj,statTypes);
		String selFGAStr = "Select MAX(id) from admin.fgatable ";
		PreparedStatement selFGAStat = conn.prepareStatement(selFGAStr);
		ResultSet res = selFGAStat.executeQuery();
		while(res.next()){
			policyName="policy"+res.getString(1);
		}
		System.out.println("policyName : "+policyName);
		String sqlStr = "Begin " +
					 " DBMS_FGA.ADD_POLICY( " +
					 " object_schema	=> ? ," +
					 " object_name => ? ," +
					 " policy_name => ? ,"+
					 " audit_condition => 'USER = ''"+grantee.toUpperCase()+"''',"+
					 " statement_types => ? );" +
					 " END ;" ;
		
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, owner.toUpperCase());
		sqlStat.setString(2, obj.toUpperCase());
		sqlStat.setString(3, policyName);
		sqlStat.setString(4, statTypes);
		sqlStat.execute();
		
		System.out.println("FGA is CREATED!");
		selFGAStat.close();
		sqlStat.close();
		
	}
	public void dropFGA(int id,String owner,String obj)throws Exception{
		String policyName = "policy"+id;
		String sqlStr = "Begin " +
				 " DBMS_FGA.DROP_POLICY( " +
				 " object_schema	=> ?, " +
				 " object_name => ?, " +
				 " policy_name => ? );"+
				 " END ;" ;
		String delStr = " delete from admin.fgatable where " +
						" id = "+id;
		
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,owner.toUpperCase());
		sqlStat.setString(2,obj.toUpperCase());
		sqlStat.setString(3,policyName);
		
		PreparedStatement delStat = conn.prepareStatement(delStr);
		sqlStat.execute();
		delStat.execute();
		delStat.close();
		sqlStat.close();
	}
	
	
	public void dropFGAPolicy(String granter, String grantee, String owner,String obj)throws Exception{
		String policyName="";
		int id =0;
		String selFGAStr = "select id from admin.fgatable where granter = ? and grantee = ? and owner = ? and obj = ?";
		PreparedStatement selFGAStat = conn.prepareStatement(selFGAStr);
		selFGAStat.setString(1,granter.toUpperCase());
		selFGAStat.setString(2,grantee.toUpperCase());
		selFGAStat.setString(3,owner.toUpperCase());
		selFGAStat.setString(4,obj.toUpperCase());
		
		ResultSet res = selFGAStat.executeQuery();
		while(res.next()){
			id = Integer.parseInt(res.getString(1));
			
		}
		policyName = "policy"+id;
		System.out.println("policyName : "+policyName);
		//String policyName = username.toLowerCase()+owner.toLowerCase()+obj.toLowerCase();
		String sqlStr = "Begin " +
				 " DBMS_FGA.DROP_POLICY( " +
				 " object_schema	=> ?, " +
				 " object_name => ?, " +
				 " policy_name => ? );"+
				 " END ;" ;
		String delStr = "delete from admin.fgatable where " +
								"id = "+id;
	
	
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		PreparedStatement delStat= conn.prepareStatement(delStr);
		
		//PreparedStatement sqlStat2 = conn.prepareStatement(delFromFGATabStr);
		sqlStat.setString(1, owner.toUpperCase());
		sqlStat.setString(2, obj.toUpperCase());
		sqlStat.setString(3, policyName);
		//sqlStat2.setString(1, policyName);
		sqlStat.execute();
		delStat.execute();
		//sqlStat2.execute();
		selFGAStat.close();
		sqlStat.close();
		delStat.close();
	}
	public LinkedList<String> getAuditedUsers(String granter,String owner, String obj) throws Exception{
		LinkedList<String> resLst = new LinkedList<String>();
		String selStr = "select grantee from admin.fgatable " +
						" where granter = ? and owner = ? and obj = ?";
		PreparedStatement selStat = conn.prepareStatement(selStr);
		selStat.setString(1,granter);
		selStat.setString(2,owner);
		selStat.setString(3,obj);
		ResultSet res = selStat.executeQuery();
		while(res.next()){
			resLst.add(res.getString(1));
		}
		System.out.println(resLst);
		selStat.close();
		return resLst;
	}
	public LinkedList<Table>getAuditedObjs(String granter,String grantee)throws Exception{
		LinkedList<Table> resLst = new LinkedList<Table>();
		String sqlStr = "select owner,obj from admin.fgatable " +
						"where granter = ? and grantee = ?	";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1,granter);
		sqlStat.setString(2,grantee);
		ResultSet res = sqlStat.executeQuery();
		while(res.next()){
			Table tab = new Table(res.getString(1),res.getString(2));
			resLst.add(tab);
		}
		printAllTable(resLst);
		sqlStat.close();
		return resLst;
		
	}
	public String getActionType(String sqlText){

		if(sqlText.contains("select"))return "SELECT";
		else if(sqlText.contains("update"))return "UPDATE";
		else if(sqlText.contains("insert"))return "INSERT";
		else return "DELETE";
	}
	public void printActionList(LinkedList<ActionLog> list){
		for(ActionLog act : list){
			System.out.println(act.username+"\t"+act.action+"\t"+act.ownerObj+"\t"+act.object+"\t"+act.time+"\t"+act.sqlStat);
		}
	}
	public String getPolicyName(String granter,String grantee,String owner,String obj)throws Exception{
		String policyName = "";
		String selIdStr = "select id from admin.fgatable where granter = ? and grantee = ? and owner = ? and obj =?";
		PreparedStatement selIdStat = conn.prepareStatement(selIdStr);
		selIdStat.setString(1,granter.toUpperCase());
		selIdStat.setString(2,grantee.toUpperCase());
		selIdStat.setString(3,owner.toUpperCase());
		selIdStat.setString(4,obj.toUpperCase());
		ResultSet res= selIdStat.executeQuery();
		while (res.next()){
			policyName = "policy"+res.getString(1);
		}
		System.out.println("POLICY NAME: "+policyName);
		return policyName.toUpperCase();
	}
	public LinkedList<ActionLog> viewAudit(String granter,String grantee,String owner,String obj,String statType )throws Exception{
		LinkedList<ActionLog> resLst = new LinkedList<ActionLog>();
		String policyName = getPolicyName(granter,grantee,owner,obj);
		String actionType = "";
		String selStr="";
		
		if(!statType.equals("ALL")){
				selStr = " select db_user,object_schema,object_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),sql_text " +
								" from dba_fga_audit_trail " +
								" where policy_name = ? " +
								" and sql_text not like '%1=1%'" +
								" and UPPER(sql_text) not like '%COUNT%'" +
								" and UPPER(sql_text) not like '%FGATABLE%'" +
								" and UPPER(sql_text) not like '%AUDITWHOLE%'" +
								" and UPPER(sql_text) not like '%STANDARD%'" +
								" and UPPER(sql_text) like '%"+statType.toUpperCase()+"%'"+
								" order by timestamp desc";
		/*if(!statType.equals("ALL")){
				selStr = " select db_user,object_schema,object_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),sql_text " +
								" from dba_fga_audit_trail " +
								" where policy_name = ? and UPPER(sql_text) like '%"+statType.toUpperCase()+"%' " +
								" order by timestamp";
			
			
		}*/
			
			
		}
		else {
			selStr = " select db_user,object_schema,object_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),sql_text " +
					" from dba_fga_audit_trail " +
					" where policy_name = ? " +
					" order by timestamp";
			
		
		}
		PreparedStatement selStat = conn.prepareStatement(selStr);
		selStat.setString(1,policyName.toUpperCase());
		ResultSet rs = selStat.executeQuery();
		
		//selStat.setString(2,statType);
	   while (rs.next()) {
		   System.out.println("Here.........");
		   if(!statType.equals("ALL"))actionType = statType;
		   else actionType = getActionType(rs.getString(5));
			 
			ActionLog log = new ActionLog(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),actionType);
			resLst.add(log);
		}
		printActionList(resLst);
		selStat.close();
		return resLst;
	}
	
/*	public void insertToUserObjTable(String username, String table)throws Exception{
		String sqlStr= "insert into UserObjTable values (?, ? )";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, username);
		sqlStat.setString(2, table);
		sqlStat.execute();
		sqlStat.close();
	}*/
	
	/*public LinkedList<Table> getObjsByUserAdminSetting(String username)throws Exception{
		LinkedList<Table> resList = new LinkedList<Table>();
		String sqlStr= "select col3,col4 from Admin.FGATable " +
						" where col1 = 'USER_ACT' and col2 = ?";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, username);
		ResultSet rs = sqlStat.executeQuery();
		while (rs.next()) {
			Table tab = new Table(rs.getString(1),rs.getString(2));
			resList.add(tab);
		}
		sqlStat.close();
		
		//System.out.println(resList);
		return resList;
	}
	public LinkedList<String> getObjByUserAdminSetting(String username)throws Exception{
		LinkedList<String> resList = new LinkedList<String>();
		String sqlStr= "select col3,col4 from Admin.FGATable " +
						" where col1 = 'USER_ACT' and col2 = ?";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, username);
		ResultSet rs = sqlStat.executeQuery();
		while (rs.next()) {
			resList.add(rs.getString(1)+"."+rs.getString(2));
		}
		sqlStat.close();
		System.out.println(resList);
		return resList;
	}
	public String getActionType(String sqlText){
		if(sqlText.contains("select"))return "SELECT";
		else if(sqlText.contains("update"))return "UPDATE";
		else if(sqlText.contains("insert"))return "INSERT";
		else return "DELETE";
	}
	public LinkedList<String> getUserByObjUserSetting(String owner,String obj)throws Exception{
		LinkedList<String> resList = new LinkedList<String>();
		String sqlStr= "select col4 from Admin.FGATable " +
						"where col1 = ? and col2 = ? and col3 = ? ";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, "USER_OBJ");
		sqlStat.setString(2, owner);
		sqlStat.setString(3, obj);
		ResultSet rs = sqlStat.executeQuery();
		while (rs.next()) {
			resList.add(rs.getString(1));
		}
		
		sqlStat.close();
		System.out.println("Audited User: "+ resList);
		return resList;
	}
	public String getPolicyName (String username,String owner,String obj,String mode){
		String policyName="";
		if(mode.equals("ADMIN_OBJ"))policyName =  "A"+owner+obj+username;			
		else if(mode.equals("USER_OBJ"))policyName = owner+obj+username;			
		else if(mode.equals("USER_ACT")) policyName = username+owner+obj;
		return policyName.toUpperCase();
	}/*
	public void printActionList(LinkedList<ActionLog> list){
		for(ActionLog act : list){
			System.out.println(act.username+"\t"+act.action+"\t"+act.ownerObj+"\t"+act.object+"\t"+act.time+"\t"+act.sqlStat);
		}
	}
	public LinkedList<ActionLog> viewAudit(String username, String owner,String obj,String statType,String mode)throws Exception{
		String actionType ="";
		LinkedList<ActionLog> resList = new LinkedList<ActionLog>();
		String policyname = getPolicyName(username,owner,obj,mode);
		System.out.println(policyname);

		String selStr="";
		if(!statType.equals("ALL")){
			selStr = " select db_user,object_schema,object_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),sql_text " +
							" from dba_fga_audit_trail " +
							" where policy_name = ? and sql_text like '%"+statType+"%' " +
							" order by timestamp";
			
			
		}
		else {
			selStr = " select db_user,object_schema,object_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),sql_text " +
					" from dba_fga_audit_trail " +
					" where policy_name = ? " +
					" order by timestamp";
			
		
		}
		PreparedStatement selStat = conn.prepareStatement(selStr);
		selStat.setString(1,policyname);
		ResultSet rs = selStat.executeQuery();
		
		//selStat.setString(2,statType);
	   while (rs.next()) {
		   if(!statType.equals("ALL"))actionType = statType;
		   else actionType = getActionType(rs.getString(5));
			 
			ActionLog log = new ActionLog(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),actionType);
			resList.add(log);
		}
		printActionList(resList);
		selStat.close();
		return resList;
	}
	
	public LinkedList<String> getUserByObjAdminSetting(String owner,String obj)throws Exception{
		LinkedList<String> resList = new LinkedList<String>();
		String sqlStr= "select col4 from Admin.FGATable " +
						"where col1 = 'ADMIN_OBJ' and col2 = ? and col3 = ? ";
		PreparedStatement sqlStat = conn.prepareStatement(sqlStr);
		sqlStat.setString(1, owner);
		sqlStat.setString(2, obj);
		ResultSet rs = sqlStat.executeQuery();
		while (rs.next()) {
			resList.add(rs.getString(1));
		}
		System.out.println(resList);
		sqlStat.close();
		return resList;
	}
	
	
	/*public void setUserOptions(String owner, String obj, String username,String statOpts)throws Exception{
		createFGAPolicy(username, obj, owner);
		String str= "insert into UserOptsTable values (?, ?, ?, ?)";
		PreparedStatement sqlStat = conn.prepareStatement(str);
		sqlStat.setString(1, owner);
		sqlStat.setString(2, obj);
		sqlStat.setString(3, username);
		sqlStat.setString(4, statOpts);
		sqlStat.execute();
		sqlStat.close();
		
	}*/
	/*public void setAdminOptions(String owner, String obj, String username,String statOpts)throws Exception{
		createFGAPolicy(username, obj, owner);
		String str= "insert into AdminOptsTable values (?, ?, ?, ?)";
		PreparedStatement sqlStat = conn.prepareStatement(str);
		sqlStat.setString(1, owner);
		sqlStat.setString(2, obj);
		sqlStat.setString(3, username);
		sqlStat.setString(4, statOpts);
		sqlStat.execute();
		sqlStat.close();
		
	}
	
	public String getUserOptions(String owner, String obj, String username) throws Exception {
		String resStr ="";
		String str= "select statOptions from UserOptsTable " +
					" where owner = ? and obj = ? and username = ? ";
		PreparedStatement sqlStat = conn.prepareStatement(str);
		ResultSet rs= sqlStat.executeQuery();
		while (rs.next()) {
			resStr = rs.getString(1);
		}
		return resStr;
	}
	public String getAdminOptions(String owner, String obj, String username) throws Exception {
		String resStr ="";
		String str= " select statOptions from AdminOptsTable " +
					" where owner = ? and obj = ? and username = ? ";
		PreparedStatement sqlStat = conn.prepareStatement(str);
		sqlStat.setString(1, owner);
		sqlStat.setString(2, obj);
		sqlStat.setString(3, username);
		ResultSet rs= sqlStat.executeQuery();
		
		while (rs.next()) {
			resStr = rs.getString(1);
		}
		return resStr;
	}
	
	public void viewStandAuditRes1(String username, String owner, String object){
		
	}*/
	
	
	
	
	//---------USAGE-----------
	public static void main(String args[]){
		
		try {
		
			Connect con = new Connect();
			con.setSID("orcl");
			con.setUser("ADMIN");
			con.setPassword("password");
		
			SQLModel db = new SQLModel(con.simple_connect());
			
		//	db.alterPassword("SALER1", "hello");
		//	db.viewChangePassAction("SALER1");
			db.dropAllFGAPolicy();
		//	db.viewChangePassAction("SALER1");
			//db.auditStandard("SALER1", "WHENEVER SUCCESSFUL");
		/*	db.noAuditStandard("SALER1");
			db.disableAllStandard();
			db.isAuditStandard("SALER1");
	//		db.enableStandard("SALER1");
		//	db.alterPassword("SALER1", "password");
		//	db.viewChangePassAction("SALER1");
			db.isAuditWhole();
			db.updateAuditWholeTable("Y");
			db.grantExtend("SALER1", "SALER2", "SALEREPORT");
			db.viewLogin("SALER1");*/
		
		//	db.disableStandard("SALER1");
		//	db.enableAllStandard();
			//db.disableAllStandard();
		//	db.enableAllFGAPolicy();
	//		db.auditStandard("SALER2", "whenever successful");
		//	db.enableAllFGAPolicy();
		//	db.dropAllFGAPolicy();
		//	db.enableAllStandard();
			//db.createFGAPolicy("ADMIN", "SALER1", "SALER2", "SALEREPORT", "INSERT");
			//db.disableFGAPolicy("policy9", "SALER2", "SALEREPORT");
			//db.auditStandard("SALER1", "WHENEVER SUCCESSFUL");
			//db.noAuditStandard("SALER1");
			//db.auditStandard("SALER1", "WHENEVER SUCCESSFUL");
			//db.alterPassword("SALER1", "password");
		//	db.viewChangePassAction("SALER1");
		//	db.createUser("HUNGCOI","password","UNLIMITED",DEF_PROFILE,DEF_ROLE);
		//	db.dropUser("HUNGCOI");
			/*
			db.createFGAPolicy("ADMIN", "SALER1", "SALER2", "SALEREPORT", "INSERT");
			 
			db.createFGAPolicy("ADMIN", "SALER3", "SALER2", "SALEREPORT", "INSERT");
			db.createFGAPolicy("SALER1", "SALER3", "SALER1", "SALEREPORT", "UPDATE");
			db.createFGAPolicy("ADMIN", "SALER1", "ADMIN", "ITEM", "UPDATE");
			db.getListAuditUsers("ADMIN", "SALER1", "SALEREPORT");*/
		//	db.getListAuditTables("ADMIN", "SALER3");
			//db.alterPassword("SALER1", "password");
			/*db.getListAuditUsers("ADMIN", "SALER2", "SALEREPORT");
			db.getAccessTablesOfUser("ADMIN");
			db.getAuditedUsers("ADMIN","ADMIN","ITEM");
			db.getAuditedObjs("ADMIN", "SALER1");
			db.viewAudit("ADMIN", "SALER1", "ADMIN", "ITEM", "UPdATE");
			db.auditLogin("SALER1","BY ACCESS");
			db.viewLogin("SALER1");*/
			/*db.dropFGAPolicy("ADMIN", "SALER1", "SALER2", "SALEREPORT");
			db.dropFGAPolicy("ADMIN", "SALER3", "SALER2", "SALEREPORT");
			db.dropFGAPolicy("SALER1", "SALER3", "SALER1", "SALEREPORT");*/
		//	db.dropFGAPolicy("ADMIN", "SALER1", "ADMIN", "ITEM");
		
		//	db.dropFGAPolicy("ADMIN", "SALER1", "SALER2", "SALEREPORT");
			/*db.connect("testuser","password");
			db.createFGAPolicy("TESTUSER", "ITEM", "ADMIN", "SELECT,UPDATE","USER_ACT");
			
			db.dropFGAPolicy("SALER1", "SALEREPORT", "SALER1", "USER_ACT");
			
			db.createFGAPolicy("TESTUSER", "ITEM", "ADMIN", "SELECT,UPDATE","USER_OBJ");
			db.createFGAPolicy("SALER1", "ITEM", "ADMIN", "SELECT","USER_OBJ");
			db.createFGAPolicy("SALER2", "ITEM", "ADMIN", "SELECT,UPDATE","USER_OBJ");
			db.createFGAPolicy("SALER1", "ITEM", "ADMIN", "SELECT,UPDATE","ADMIN_OBJ");
			db.createFGAPolicy("SALER3", "ITEM", "ADMIN", "SELECT","ADMIN_OBJ");
			db.createFGAPolicy("SALER1", "SALEREPORT", "SALER1", "SELECT,INSERT,UPDATE,DELETE","USER_ACT");
			db.createFGAPolicy("SALER1", "SALEREPORT", "SALER2", "SELECT,INSERT,UPDATE,DELETE","USER_ACT");
			db.createFGAPolicy("TESTUSER", "ITEM", "ADMIN", "SELECT,UPDATE","USER_ACT");
		
			db.dropFGAPolicy("TESTUSER", "ITEM", "ADMIN", "USER_OBJ");
			db.dropFGAPolicy("SALER1", "ITEM", "ADMIN","USER_OBJ");
			db.dropFGAPolicy("SALER2", "ITEM", "ADMIN", "USER_OBJ");
			
			db.dropFGAPolicy("SALER1", "ITEM", "ADMIN", "ADMIN_OBJ");
			db.dropFGAPolicy("SALER3", "ITEM", "ADMIN", "ADMIN_OBJ");
			
			db.dropFGAPolicy("SALER1", "SALEREPORT", "SALER1", "USER_ACT");
			db.dropFGAPolicy("SALER1", "SALEREPORT", "SALER2", "USER_ACT");
			db.dropFGAPolicy("TESTUSER", "ITEM", "ADMIN", "USER_ACT");
			
		
		
			
			db.getUserByObjUserSetting("ADMIN", "ITEM");
			db.getUserByObjAdminSetting("ADMIN","ITEM");
			db.getObjByUserAdminSetting("SALER1");
			db.viewAudit("SALER1", "ADMIN", "ITEM", "select","USER_OBJ");
			System.out.println("----------------------------");
			db.viewAudit("SALER1", "ADMIN", "ITEM", "select","ADMIN_OBJ");
			
			///db.viewAudit("TESTUSER", "ADMIN", "ITEM", "ALL","USER_ACT");
			
			//db.setAdminOptions("SALER1", "SALE_REPORT_TAB", "TESTUSER", "SELECT,UPDATE");
			//db.selectAccount("Minh");
			//db.createUser("Huy","password","UNLIMITED",DEF_PROFILE,DEF_ROLE);
			//db.createUser("HUNG","password","UNLIMITED",DEF_PROFILE,DEF_ROLE);
			db.isExistingUser("Hung");
			db.isExistingUser("minh");
			db.alterPassword("Hung", "newpass");
			db.alterOtherInfo("Huy", "LOCK", "100M", DEF_ROLE, DEF_PROFILE);
			db.getAllTablesOfUser("Hung");
			//db.getAllTables();
			//db.getAllTable();
		//	db.auditAllByUser("Hung");
			db.getUnAuditedObjAdminSetting("HUNG");
			db.getUnAuditedUserAdminSetting("ADMIN","ITEM");
			db.getUnAuditedUserUserSetting("ADMIN", "ITEM");
			//db.dropAuditAllByUser("Hung");
			//db.dropUser("hung");
			//db.dropUser("Huy");
			db.createUser("HUNG","password","UNLIMITED",defProfile,defRole);
			db.createUser("Ngan","password","100M",defProfile,defRole);
			db.createUser("Minh","password","100M",defProfile,defRole);
			db.isExistingUser("Minh");
			db.getAllTablesOfUser("SALER1");
			//db.getUserInfo("huY");
		//	db.alterOtherInfo("NGAN", "LOCK", "UNLIMITED", defRole, defProfile);
		//	db.alterPassword("Huy", "newPass");
		//	db.dropUser("HUy");
			db.getAllTables();
			db.auditAllByUser("ngan", "by access", " ");
			//db.createFGAPolicy("SALE_MANAGER", "TEST_TAB", "USER1", "insert,update,delete,select");
			db.dropFGAPolicy("SALE_MANAGER", "TEST_TAB", "USER1");*/
			db.conn.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
